package com.louis.jdbc;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;

import java.util.List;


/**
 * @author XRY
 * @date 2023年06月27日20:18
 */
@SpringJUnitConfig(locations = "classpath:bean.xml")
public class JDBCTemplateTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;


    //添加、修改和删除
    @Test
    public void testUpdate(){
       /* //1、添加操作
        //①编写sql
        String sql = "insert into t_emp values(null, ?, ?, ?)";
        //②调用jdbcTemplate中的方法，传入相关的参数
//        Object[] params = {"小明", 23, "男"};
        int effect = jdbcTemplate.update(sql, params);
        int effect1 = jdbcTemplate.update(sql, "小明", 23, "男");
        int effect2 = jdbcTemplate.update(sql, "louie", 24, "男");
        int effect3 = jdbcTemplate.update(sql, "Alex", 22, "男");
//        System.out.println("effect = " + effect1);
        /*effect = 1*/
        /*effect1 = 1*/
       
       /* //2、修改操作
        String sql = "update t_emp set name = ? where id = ?";
        int row = jdbcTemplate.update(sql, "Khan", 2);
        System.out.println("row = " + row);    
        /*row = 1*/
        
        //3、删除
        String sql = "delete from t_emp where id = ?";
        int delete = jdbcTemplate.update(sql, 2);
        System.out.println("delete = " + delete);
        /*delete = 1*/
    }

    //查询返回一个对象
    @Test
    public void testSelectObject(){
        String sql = "select * from t_emp where id = ?";
        /*//写法一
        //RowMapper用来对象封装
        Emp empResult = jdbcTemplate.queryForObject(sql,
                (rs, rowNum) -> {
                    Emp emp = new Emp();
                    emp.setId(rs.getInt("id"));
                    emp.setName(rs.getString("name"));
                    emp.setAge(rs.getInt("age"));
                    emp.setSex(rs.getString("sex"));
                    return emp;
                }, 1);
        System.out.println("empResult = " + empResult);*/
        /*empResult = Emp{id=1, name='小明', age=23, sex='男'}*/
        //写法二
        Emp result = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Emp.class), 1);
        System.out.println(result);
        /*Emp{id=1, name='小明', age=23, sex='男'}*/
    }
    //查询返回list集合
    @Test
    public void testSelectList(){
        String sql = "select * from t_emp";
        List<Emp> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Emp.class));
        for (Emp emp : list) {
            System.out.println("emp = " + emp);
        }
        /*
        emp = Emp{id=1, name='小明', age=23, sex='男'}
        emp = Emp{id=3, name='louie', age=24, sex='男'}
        emp = Emp{id=4, name='Alex', age=22, sex='男'}
        * */
    }
    //返回单个值
    @Test
    public void testSelectOne(){
        String sql = "select count(1) from t_emp";
        Integer sum = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println("sum = " + sum);
        /*sum = 3*/
    }

}
